package com.elite.groovy.dev;

import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.SQLSelect;
import com.alibaba.druid.sql.ast.statement.SQLSelectQuery;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class DevRPTTest {
    private static final Log log = LogFactory.getLog(DevRPTTest.class);
    private static JdbcTemplate jt = TxConfigUAT.getJdbcTemplate();
    private static long startTime = System.currentTimeMillis();
    private static long costTime = System.currentTimeMillis();

    public static void main(String[] args) throws Exception {

        long total_Cost = 0;
        String sel_SQL1 = "Select \n" + "        1,2,3\n" + "From Objective o\n" + "        Left Join Tabcorder_Zts Tz On Tz.Relation_Id = o.Objective_Guid\n" + "        Left Join Customer c On c.Customer_Guid = o.Customer_Guid\n" + "        Left Join Custom_Parameter Cp On Cp.Parameter_Id = o.Coverage_Name And Cp.Parameter_Type = 'coverage'\n" + "        Left Join Staff s On s.Staff_Id = o.Createdby\n" + "        Left Join Workorderstep3 W3 On W3.Workorder_Guid = o.Objective_Guid\n" + "        Left Join Wo3_Frame_Map Fm On Fm.Map_Id = o.Workorderframe_Id\n" + "        Left Join Wo3_Link_Map Wol On Wol.Map_Id = o.Map_Id And Source_Id = 'BEGIN'\n" + "        Left Join Wo3_Node_Map Nm On o.Curstep_Id_Map = Nm.Nodemap_Id And Nm.Map_Id = Fm.Map_Id\n" + "        Left Join (Select 2 As Workorderids, Xmlagg(Xmlparse(Content Mapname || ',' Wellformed)) As Mapnames, \n" + "                                        Policyno\n" + "                                From Objective o\n" + "                                        Left Join Wo3_Frame_Map Fm On Fm.Map_Id = o.Workorderframe_Id\n" + "                                Where Fm.Category = 'WORDER'\n" + "                                And Policyno Is Not Null\n" + "                                Group By Policyno) t \n" + "                        On t.Policyno = o.Policyno\n" + "        Left Join Custom_Parameter Cp3 On Cp3.Parameter_Type = 'hanle_reusltparent' And Cp3.Parameter_Id = Tz.Result1\n" + "        Left Join Custom_Parameter Cp4 On Cp4.Parameter_Type = 'hanle_reuslt' And Cp4.Parameter_Id = Tz.Result2\n" + "        Left Join Custom_Parameter Cp7 On Cp7.Parameter_Id = Tz.Examine_Type And Cp7.Parameter_Type = 'examinename_type'\n" + "        Left Join Custom_Parameter Cp8 On Tz.Loss_Type = Cp8.Parameter_Id And Cp8.Parameter_Type = 'loss_type'\n" + "        Left Join Custom_Parameter Cp9 On o.Coverage_Name = Cp9.Parameter_Id And Cp9.Parameter_Type = 'coverage'\n" + "  Left Join (Select Relation_Id,\n" + "                                Sum(Case When tzh.Delay_Flag = 1 Then 1 Else 0 End) Del\n" + "                   From Objective o\n" + "                                Left Join Tabcorder_Accuse_His tzh On tzh.Relation_Id = o.Objective_Guid\n" + "                   Where 1 = 1 \n" + "                   Group By Relation_Id) Tzh \n" + "        On tzh.Relation_Id = o.Objective_Guid\n" + "Where o.Workorderframe_Id = '3EN351' \n" + "Group By o.Workorderid, s.Agent_Id, s.Staffname, o.Organizaname, Tz.Arrive_Time, o.callby,o.calltel, \n" + "    o.Policyno, o.Insured, Tz.Rule_Parent, Tz.Rule, Tz.Reason_Name, Tz.Content, o.Createddate, o.Objectivestatus, \n" + "    o.Modifieddate, o.Urgeflag, o.Reopen, Tz.Cancelstatus, o.Obj_Flag, o.Uplevel_Type, o.Workorderid, \n" + "    Tz.Report_Pkey, o.Objectivestatus, Nm.Nodemapname, Wol.Target_Id, o.Curstep_Id_Map, o.Bzgdflag, To_Char(t.Workorderids),\n" + "    To_Char(t.Mapnames), o.Reportno, Tz.Examinename, Cp7.Parameter_Desc, Tz.Losser, Cp8.Parameter_Desc, o.Coverage_Name, \n" + "    Cp9.Parameter_Desc, Tz.Organ_Idea, Cp3.Parameter_Desc, Cp4.Parameter_Desc, Tz.Management, o.ts_customertype,Tzh.Del,o.Coverage_Name \n" + "Order By o.Createddate";

        log.debug("ℹ查询次数:" + sel_SQL1 + " ⌛平均耗时:" + parseSQLSelectQuery(sel_SQL1,"oceanbase"));

    }


    private static SQLSelectQuery parseSQLSelectQuery(String sql, String dbProductName) {
        log.debug("dbProductName:" + dbProductName);
        sql = parseSqlCondition(sql);

        SQLStatement sqlStatement = SQLUtils.parseSingleStatement(sql, DbType.oracle, true);
        SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) sqlStatement;
        SQLSelect select = sqlSelectStatement.getSelect();
        return select.getQuery();
    }



    public static String parseSqlCondition(String source) {
        try {
            source = source.replaceAll("(?i)\\[epidtb]", "").replaceAll("(?i)\\[epid]", "").replaceAll("(?i)\\[staffid]", "").replaceAll("(?i)\\[rolegroupid]", "");
            source = source.replaceAll("(?i)\\{C_EPIDTB}", "").replaceAll("(?i)\\{C_EPID}", "").replaceAll("(?i)\\{C_GRP}", "").replaceAll("(?i)\\{C_BY}", "").replaceAll("(?i)\\{C_NOW}", "").replaceAll("(?i)\\{C_LANG}", "").replaceAll("(?i)\\{C_GUID}", "").replaceAll("(?i)\\{C_SFID}", "");

            source = source.replaceAll("(?s)%B%.*?%E%", "");

            Pattern p = Pattern.compile("!\\((\\w+)\\)");
            Matcher matcher = p.matcher(source);
            while (matcher.find()) {
                int gc = matcher.groupCount();
                if (gc >= 1) {
                    String exp = matcher.group(1);
                    source = source.replaceAll("!\\(" + exp + "\\)", exp);
                }
            }
            Pattern pattern = Pattern.compile("#\\((.*?)\\)");
            Matcher match = pattern.matcher(source);
            while (match.find()) {
                int groupCount = match.groupCount();
                if (groupCount >= 1) {
                    String exp = match.group(1);
                    source = source.replaceAll("#\\(" + exp + "\\)", "");
                }
            }
        } catch (Exception e) {
            log.error("", e);
        }
        return source;
    }

}
